﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;
using System.Data.SqlClient;
using BLL;

namespace BLL
{
    public class BAIVIETBLL
    {
        DataService DB = new DataService();
        PHUMUCBLL pmBll = new PHUMUCBLL();
        THANHVIENBLL tvBll = new THANHVIENBLL();

        public BAIVIET getBestNewBaivietWithIdPhumuc(int idPhumuc) {
            string sql = "select top 1 bv.mabaiviet, bv.tenbaiviet, bv.noidung, bv.maphumuc, bv.mathanhvien, bv.ngaylap, bv.matinhtrangbv, bv.listchat, bv.xem "
                + " from phumuc pm,baiviet bv where pm.maphumuc = bv.maphumuc and pm.maphumuc = " + idPhumuc.ToString() + " order by bv.ngaylap desc";
            try
            {
                DataTable table = new DataTable();
                table = DB.ExecuteReader(sql);

                BAIVIET bv = new BAIVIET();
                DataRow r = table.Rows[0];

                bv.mabaiviet = (int)r[0];
                bv.tenbaiviet = (string)r[1];
                try { bv.noidung = (string)r[2]; }
                catch { bv.noidung = ""; };
                bv.maphumuc = (int)r[3];
                bv.mathanhvien = (int)r[4];
                bv.ngaylap = Convert.ToDateTime(r[5].ToString());
                bv.matinhtrangbv = (int)r[6];
                
                try { bv.listchat = (string)r[7]; }
                catch { bv.listchat = ""; }

                bv.xem = (int)r[8];

                return bv;
            }
            catch { return null; }
        }

        public BAIVIET getBestNewBaiviet(){
            string sql = "select top 1 * from baiviet order by ngaylap desc";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            BAIVIET bv = new BAIVIET();
            DataRow r = table.Rows[0];

            bv.mabaiviet = (int)r[0];
            bv.tenbaiviet = (string)r[1];
            try { bv.noidung = (string)r[2]; }
            catch { bv.noidung = ""; };
            bv.maphumuc = (int)r[3];
            bv.mathanhvien = (int)r[4];
            bv.ngaylap = Convert.ToDateTime(r[5].ToString());
            bv.matinhtrangbv = (int)r[6];

            try { bv.listchat = (string)r[7]; }
            catch { bv.listchat = ""; }

            bv.xem = (int)r[8];

            return bv;
        }

        public List<BAIVIET> getListBaivietForPaging(int pageSize, int CurrentIndex, ref int itemCount)
        {
            DataTable table = new DataTable(); 

            SqlParameter[] para = new SqlParameter[3];
            para[0] = new SqlParameter(@"PageSize", pageSize);
            para[1] = new SqlParameter(@"CurrentPage", CurrentIndex);
            para[2] = new SqlParameter(@"ItemCount", SqlDbType.Int);
            para[2].Direction = ParameterDirection.Output;

            table = DB.ExecuteReaderProceduce("dbo.pro_getPagedBaivietByDate", ref para);

            itemCount = Int32.Parse(para[2].Value.ToString());
           
            List<BAIVIET> ListBv = new List<BAIVIET>();
            foreach(DataRow r in table.Rows){
                BAIVIET bv = new BAIVIET();
                bv.mabaiviet = (int)r[0];
                bv.tenbaiviet = (string)r[1];
                try { bv.noidung = (string)r[2]; }
                catch { bv.noidung = ""; };
                bv.maphumuc = (int)r[3];
                bv.mathanhvien = (int)r[4];
                bv.ngaylap = Convert.ToDateTime(r[5].ToString());
                bv.matinhtrangbv = (int)r[6];
                try { bv.listchat = (string)r[7]; }
                catch { bv.listchat = ""; }
                bv.xem = (int)r[8];
                ListBv.Add(bv);
            }
            return ListBv;
        }

        //get all values
        public List<BAIVIET> getALL()
        {
            string sql = "select * from baiviet";
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<BAIVIET> ListBv = new List<BAIVIET>();
            foreach (DataRow r in table.Rows)
            {
                BAIVIET bv = new BAIVIET();
                bv.mabaiviet = (int)r[0];
                bv.tenbaiviet = (string)r[1];
                try { bv.noidung = (string)r[2]; }
                catch { bv.noidung = ""; };
                bv.maphumuc = (int)r[3];
                bv.mathanhvien = (int)r[4];
                bv.ngaylap = Convert.ToDateTime(r[5].ToString());
                bv.matinhtrangbv = (int)r[6];
                try { bv.listchat = (string)r[7]; }
                catch { bv.listchat = ""; }
                bv.xem = (int)r[8];
                ListBv.Add(bv);
            }
            return ListBv;
        }

        //insert value
        public void insert(string tenbaiviet, string noidung, int maphumuc, int mathanhvien, DateTime ngaylap, int matinhtrangbv, string listchat,int xem)
        {

            string sql = "insert into baiviet values (@tenbaiviet,@noidung,@maphumuc,@mathanhvien,@ngaylap,@matinhtrangbv,@listchat,@xem)";
            SqlParameter paraTen = new SqlParameter("tenbaiviet", tenbaiviet);
            SqlParameter paraNd = new SqlParameter("noidung", noidung);
            SqlParameter paraMpm = new SqlParameter("maphumuc", maphumuc);
            SqlParameter paraMtv = new SqlParameter("mathanhvien", mathanhvien);
            SqlParameter paraNl = new SqlParameter("ngaylap", ngaylap);
            SqlParameter paraMttbv = new SqlParameter("matinhtrangbv", matinhtrangbv);
            SqlParameter paraLischat = new SqlParameter("listchat", listchat);
            SqlParameter paraXem = new SqlParameter("xem", xem);

            DB.ExecuteNonQuery(sql, paraTen, paraNd, paraMpm, paraMtv, paraNl, paraMttbv, paraLischat, paraXem);

        }

        public void update(int mabaiviet, string tenbaiviet, string noidung, int maphumuc, int mathanhvien, DateTime ngaylap, int matinhtrangbv, string listchat, int xem)
        {
            string sql = "update baiviet set tenbaiviet = N'" + tenbaiviet + "' ,noidung = N'" + noidung + "' , maphumuc = " + maphumuc.ToString() + " , mathanhvien = ' " + mathanhvien.ToString() + "' , ngaylap = '" + ngaylap.ToString() + "', matinhtrangbv = " + matinhtrangbv.ToString() + ", listchat = '" + listchat + "', xem = " + xem.ToString() + " where mathanhvien = " + mathanhvien.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public void delete(int mabaiviet)
        {
            string sql = "delete from baiviet where mabaiviet = " + mabaiviet.ToString();
            DB.ExecuteNonQuery(sql);
        }

        //get count
        public int count()
        {
            string sql = "select count(*) from baiviet";
            return Int32.Parse(DB.ExecuteScalar(sql));
        }

        public int getCountBaivietWithIDThanhvien(int idTHanhvien) {
            string sql = "select count(*) from baiviet bv where bv.mathanhvien = " + idTHanhvien.ToString();
            return Int32.Parse( DB.ExecuteScalar(sql));
        }

        public List<BAIVIET> getBaivietWithIDPm(int maPhumuc)
        {
            string sql = "select mabaiviet,tenbaiviet,noidung,bv.maphumuc,mathanhvien,ngaylap,matinhtrangbv,listchat,xem "
                        + "from	baiviet bv,phumuc pm "
                        + "where bv.maphumuc = pm.maphumuc and pm.maphumuc = " + maPhumuc.ToString();

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<BAIVIET> ListBv = new List<BAIVIET>();
            foreach (DataRow r in table.Rows)
            {
                BAIVIET bv = new BAIVIET();
                bv.mabaiviet = (int)r[0];
                bv.tenbaiviet = (string)r[1];
                try { bv.noidung = (string)r[2]; }
                catch { bv.noidung = ""; };
                bv.maphumuc = (int)r[3];
                bv.mathanhvien = (int)r[4];
                bv.ngaylap = Convert.ToDateTime(r[5].ToString());
                bv.matinhtrangbv = (int)r[6];

                try { bv.listchat = (string)r[7]; }
                catch { bv.listchat = ""; }

                bv.xem = (int)r[8];
                ListBv.Add(bv);
            }
            return ListBv;
        }

        public List<BAIVIET> getTopNBaiviet(int nCount)
        {
            string sql = "select top " + nCount.ToString() + " * from baiviet order by ngaylap desc";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<BAIVIET> ListBv = new List<BAIVIET>();
            foreach (DataRow r in table.Rows)
            {
                BAIVIET bv = new BAIVIET();
                bv.mabaiviet = (int)r[0];
                bv.tenbaiviet = (string)r[1];
                try { bv.noidung = (string)r[2]; }
                catch { bv.noidung = ""; };
                bv.maphumuc = (int)r[3];
                bv.mathanhvien = (int)r[4];
                bv.ngaylap = Convert.ToDateTime(r[5].ToString());
                bv.matinhtrangbv = (int)r[6];

                try { bv.listchat = (string)r[7]; }
                catch { bv.listchat = ""; }

                bv.xem = (int)r[8];
                ListBv.Add(bv);
            }
            return ListBv;
        }

        public string getTinhtrangWithID(int mabaiviet) {
            string sql = "select tentinhtrangbv from tinhtrangbv ttbv,baiviet bv where ttbv.matinhtrangbv = bv.matinhtrangbv and bv.mabaiviet = '" + mabaiviet.ToString() + "'";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            return table.Rows[0][0].ToString();
        }

        //get final baiviet
        public BAIVIET getFinalBaiviet() {
            string sql = "select top 1 mabaiviet from baiviet order by mabaiviet desc";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            BAIVIET bv = new BAIVIET();
            DataRow r = table.Rows[0];

            bv.mabaiviet = (int)r[0];
            bv.tenbaiviet = (string)r[1];
            try { bv.noidung = (string)r[2]; }
            catch { bv.noidung = ""; };
            bv.maphumuc = (int)r[3];
            bv.mathanhvien = (int)r[4];
            bv.ngaylap = Convert.ToDateTime(r[5].ToString());
            bv.matinhtrangbv = (int)r[6];

            try { bv.listchat = (string)r[7]; }
            catch { bv.listchat = ""; }

            bv.xem = (int)r[8];
            return bv;
        }

        public void updateListChatWithIDBaiviet(string listchat,int idBaiviet){
            string sql = "update baiviet set listchat = '" + listchat + "' where mabaiviet = " + idBaiviet;
            DB.ExecuteNonQuery(sql);
        }

        public BAIVIET getBaivietWithIDBaiviet(int idBaiviet) {
            string sql = "select * from baiviet where mabaiviet = " + idBaiviet.ToString();

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            BAIVIET bv = new BAIVIET();
            DataRow r = table.Rows[0];

            bv.mabaiviet = (int)r[0];
            bv.tenbaiviet = (string)r[1];
            try { bv.noidung = (string)r[2]; }
            catch { bv.noidung = ""; };
            bv.maphumuc = (int)r[3];
            bv.mathanhvien = (int)r[4];
            bv.ngaylap = Convert.ToDateTime(r[5].ToString());
            bv.matinhtrangbv = (int)r[6];

            try { bv.listchat = (string)r[7]; }
            catch { bv.listchat = ""; }
            bv.xem = (int)r[8];
            return bv;
        }

        public DataTable timkiem(string sql)
        {
           
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            return table;
        }

        public void addThanhvienToListchatWithIDBaiviet(THANHVIEN tv, int idBaiviet) {
            BAIVIET bv = this.getBaivietWithIDBaiviet(idBaiviet);

            string[] str_tam = this.ConvertToArray(bv.listchat, ",");

            for (int i = 0; i < str_tam.Length; i++ )
            {
                string str = str_tam[i];
                if (str == tv.mathanhvien.ToString()) return;
            }

            if (bv.listchat == "") { bv.listchat = tv.mathanhvien.ToString(); }
            else { bv.listchat +=("," + tv.mathanhvien.ToString());}

            this.updateListChatWithIDBaiviet(bv.listchat, bv.mabaiviet);
        }

        public void removeThanhvienToListchatWithIDBaiviet(THANHVIEN tv, int idBaiviet)
        {
            BAIVIET bv = this.getBaivietWithIDBaiviet(idBaiviet);

            if (bv.listchat == "") return;

            string[] str_tam = this.ConvertToArray(bv.listchat, ",");

            if (str_tam.Length == 1)
            {
                bv.listchat = "";
            }
            else {
                List<string> str_new_list = new List<string>();

                for (int i = 0; i < str_tam.Length; i++)
                {
                    if(str_tam[i] != tv.mathanhvien.ToString())str_new_list.Add(str_tam[i]);
                }

                bv.listchat = this.ConvertStringArrayToStringJoin(str_new_list.ToArray(), ",");
            }

            this.updateListChatWithIDBaiviet(bv.listchat, bv.mabaiviet);
        }

        public bool checkThanhvienIsInGroupWithIDBaiviet(THANHVIEN tv, int idBaiviet)
        {
            BAIVIET bv = this.getBaivietWithIDBaiviet(idBaiviet);

            if (bv.listchat == "") return false;

            string[] str_tam = this.ConvertToArray(bv.listchat, ",");
            for (int i = 0; i < str_tam.Length; i++)
            {
                string str = str_tam[i];
                if (str == tv.mathanhvien.ToString()) return true;
            }

            return false;
        }

        public List<THANHVIEN> getThanhviensInGroupWithIDBaiviet(int idBaiviet) {
            string sql = "select listchat from baiviet where mabaiviet = " + idBaiviet.ToString();

            DataTable table = new DataTable();
            string listchat = DB.ExecuteScalar(sql);

            string[] listArray = this.ConvertToArray(listchat, ",");

            List<THANHVIEN> ListTV = new List<THANHVIEN>();
            for (int i = 0; i < listArray.Length; i++)
            {
                THANHVIEN tv = new THANHVIEN();
                tv = tvBll.getThanhvienWithIDThanhvien(Int32.Parse(listArray[i]));
                ListTV.Add(tv);
            }

            return ListTV;
        }

        //process listchat
        private string[] ConvertToArray(string strIds, string separator)
        {
            int size = (strIds != null && strIds.Length > 0)
                        ? 1
                        : 0;
            //Determine how many semi-colon separated values are in string
            string temp = strIds;
            int pos;
            while (temp.IndexOf(separator) > -1)
            {
                size++;
                pos = temp.IndexOf(separator);
                temp = temp.Substring(pos + 1).Trim();
            }
            string[] strArray = new string[size];
            for (int i = 0; i < size; i++)
            {

                pos = (strIds.Trim().IndexOf(separator) == -1)
                        ? strIds.Trim().Length
                        : strIds.IndexOf(separator);
                //Now get the string within the single quotes; trimming them off
                string val = strIds.Trim().Substring(0, pos).Trim();
                strArray[i] = val;
                if (strIds.Length > (pos + 1))
                {
                    strIds = strIds.Substring(pos + 1).Trim();
                }
            }
            return strArray;
        }

        private string ConvertStringArrayToStringJoin(string[] array, string separator)
        {
            //
            // Use string Join to concatenate the string elements.
            //
            string result = string.Join(separator, array);
            return result;
        }

        public THANHVIEN getThanhvienWithIDBaiviet(int idBaiviet){
            string sql = "select tv.mathanhvien, tv.tenthanhvien, tv.chucvu, tv.email, tv.hinh from	baiviet bv, thanhvien tv "
                        + " where bv.mathanhvien = tv.mathanhvien and bv.mabaiviet = " + idBaiviet.ToString();

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            THANHVIEN tv = new THANHVIEN();
            DataRow r = table.Rows[0];

            tv.mathanhvien = Int32.Parse(r[0].ToString());
            tv.tenthanhvien = r[1].ToString();
            tv.chucvu = r[2].ToString();
            tv.email = r[3].ToString();
            try { tv.hinh = r[4].ToString(); }
            catch { tv.hinh = ""; }

            return tv;
        }

        public void updateValueXemWithIDBaiviet(int idBaiviet, int valueXem) {
            string sql = "update baiviet set xem = '" + valueXem.ToString() +  "'  where mabaiviet = " + idBaiviet.ToString();
            DB.ExecuteNonQuery(sql);
        }
        public void setTinhtrangBaiviet(int idBaiviet, int idTinhtrang)
        {
            string sql = "update baiviet set matinhtrangbv = " + idTinhtrang.ToString() + " where mabaiviet = " + idBaiviet.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public void DeleteBaivietWithId(int idBaiviet) {
            string sql = "delete from baiviet where mabaiviet = " + idBaiviet;
            DB.ExecuteNonQuery(sql);
        }

        public bool isBlockWithIDBaiviet(int idbaiviet) {
            string sql = "select tentinhtrangbv from baiviet bv,tinhtrangbv ttbv "
                        + " where bv.matinhtrangbv = ttbv.matinhtrangbv and bv.mabaiviet = " + idbaiviet.ToString();

            string kq = DB.ExecuteScalar(sql);
            if (kq == "Block") return true;
            else return false;
        }

        public List<BAIVIET> getListBaivietWithSQLString(string sql) {
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<BAIVIET> ListBv = new List<BAIVIET>();
            foreach (DataRow r in table.Rows)
            {
                BAIVIET bv = new BAIVIET();
                bv.mabaiviet = (int)r[0];
                bv.tenbaiviet = (string)r[1];
                try { bv.noidung = (string)r[2]; }
                catch { bv.noidung = ""; };
                bv.maphumuc = (int)r[3];
                bv.mathanhvien = (int)r[4];
                bv.ngaylap = Convert.ToDateTime(r[5].ToString());
                bv.matinhtrangbv = (int)r[6];

                try { bv.listchat = (string)r[7]; }
                catch { bv.listchat = ""; }

                bv.xem = (int)r[8];
                ListBv.Add(bv);
            }
            return ListBv;
        }

        public List<BAIVIET> getListBaivietWithIDthanhvien(int idThanhvien)
        {
            string sql = "select bv.mabaiviet,bv.tenbaiviet,bv.noidung, bv.maphumuc, bv.mathanhvien,  bv.ngaylap,bv.matinhtrangbv,bv.listchat,bv.xem "
                        + " from baiviet bv,thanhvien tv where bv.mathanhvien = tv.mathanhvien and tv.mathanhvien  =  " + idThanhvien.ToString();

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<BAIVIET> ListBv = new List<BAIVIET>();
            foreach (DataRow r in table.Rows)
            {
                BAIVIET bv = new BAIVIET();
                bv.mabaiviet = (int)r[0];
                bv.tenbaiviet = (string)r[1];
                try { bv.noidung = (string)r[2]; }
                catch { bv.noidung = ""; };
                bv.maphumuc = (int)r[3];
                bv.mathanhvien = (int)r[4];
                bv.ngaylap = Convert.ToDateTime(r[5].ToString());
                bv.matinhtrangbv = (int)r[6];

                try { bv.listchat = (string)r[7]; }
                catch { bv.listchat = ""; }

                bv.xem = (int)r[8];
                ListBv.Add(bv);
            }
            return ListBv;
        }

        public List<TOPPOSTER> getTopNPoster(int count) {
            string sql = "select top " + count.ToString() + " tv.tenthanhvien , count(bv.mabaiviet) as tongso " +
                        " from baiviet bv right outer join thanhvien tv on bv.mathanhvien = tv.mathanhvien " +
                        " group by tv.tenthanhvien order by tongso desc ";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<TOPPOSTER> listPoster = new List<TOPPOSTER>();
            foreach(DataRow r in table.Rows){
                TOPPOSTER pos = new TOPPOSTER();
                pos.tenthanhvien = r[0].ToString();
                pos.sobai = Int32.Parse(r[1].ToString());
                listPoster.Add(pos);

            }

            return listPoster;

        }

        public List<BAIVIET> getTopNXem(int count) {
            string sql = "select top " + count + " * from baiviet order by xem desc";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<BAIVIET> ListBv = new List<BAIVIET>();
            foreach (DataRow r in table.Rows)
            {
                BAIVIET bv = new BAIVIET();
                bv.mabaiviet = (int)r[0];
                bv.tenbaiviet = (string)r[1];

                try { bv.noidung = (string)r[2]; }
                catch { bv.noidung = ""; };
                bv.maphumuc = (int)r[3];
                bv.mathanhvien = (int)r[4];
                bv.ngaylap = Convert.ToDateTime(r[5].ToString());
                bv.matinhtrangbv = (int)r[6];

                try { bv.listchat = (string)r[7]; }
                catch { bv.listchat = ""; }

                bv.xem = (int)r[8];
                ListBv.Add(bv);
            }

            return ListBv;
        }
    }
}
